/* Dwarf_Schema.sql */

---------------------------------------------------------------------------------------
create table dwarves
(
	id		/* int */		serial		primary key,
	name		varchar(80)	unique		not null,
	born		int				not null,
	mountain_id	int				null,		/* mountains.id */
	spouse_id	int				null		/* dwarves.id */
);

alter table dwarves add
	constraint dwarves_spouse_fk foreign key (spouse_id)
	references dwarves (id);

grant all on dwarves to public;
\g

grant all on dwarves_id_seq to public;
\g

---------------------------------------------------------------------------------------
create table mountains
(
	id		/* int */		serial		primary key,
	name		varchar(80)	unique		not null,
	location	varchar(80)			not null,
	king_id		int				null		/* dwarves.id */
);

alter table dwarves add
	constraint dwarves_mountain_fk foreign key (mountain_id)
	references mountains (id);

alter table mountains add
	constraint mountain_king_fk foreign key (king_id)
	references dwarves (id);

grant all on mountains to public;
\g

grant all on mountains_id_seq to public;
\g

---------------------------------------------------------------------------------------
/* mountains visited by dwarves */
create table visits
(
	dwarf_id	int		not null,
	mountain_id	int		not null
);

alter table visits add
	constraint pk_visit
	primary key (dwarf_id,mountain_id);
grant all on visits to public;

alter table visits add
	constraint visit_dwarves_fk foreign key (dwarf_id)
	references dwarves (id);

alter table visits add
	constraint visit_mountain_fk foreign key (mountain_id)
	references mountains (id);
\g